# 向数据库添加数据模块
class AddDataToBase:

    # 创建报价表
    #   参数:
    #       id:客户工号
    def create_bill_table(id):
        sql = """CREATE TABLE IF NOT EXISTS customer_quotation.obj_{id}(
                        id INT(0) PRIMARY KEY AUTO_INCREMENT,
                        obj_id VARCHAR(255),
                        states VARCHAR(255),
                        head_kg DOUBLE(255,3),
                        stage_min DOUBLE(255,3),
                        stage_max DOUBLE(255,3),
                        head_price DOUBLE(255,3),
                        cte_kg DOUBLE(255,3),
                        cte_fixed DOUBLE(255,3),
                        cte_unit DOUBLE(255,3),
                        carry DOUBLE(255,3),
                        store VARCHAR(255));""".format(id=id.strip().replace(' ', ''))
        return sql


    # 判断报价表是否存在
    #   参数:
    #       id:客户工号
    def isornotexist_table(id):
        sql = """SELECT ISNULL((SELECT table_name
                 FROM information_schema.TABLES
                 WHERE table_name = "obj_{id}"));""".format(id=id.strip().replace(' ', ''))
        return sql

    # 报价表插入数据
    #   参数:
    #       id:客户工号
    #       states:省份
    #       head_kg:首重
    #       stage_min:阶段最小重量
    #       stage_max:阶段最大重量
    #       head_price:首重价格
    #       cte_kg:续重(按重量)
    #       cte_fixed:续重(阶段内固定)
    #       cte_unit:计算单位
    #       carry:进位
    #       store:店铺
    def inster_data_tobill(id, states, head_kg,
                           stage_min, stage_max,
                           head_price, cte_kg,
                           cte_fixed, cte_unit,
                           carry, store):
        sql = """INSERT INTO customer_quotation.obj_{id}(obj_id, states, 
                                                         head_kg, stage_min, 
                                                         stage_max, head_price,
                                                         cte_kg, cte_fixed,
                                                         cte_unit, carry, store)
                 VALUE("75512.{id}", "{states}", {head_kg},
                       {stage_min}, {stage_max}, {head_price},
                       {cte_kg}, {cte_fixed}, {cte_unit},
                       {carry},"{store}");""".format(id=id, states=states, head_kg=head_kg, stage_min=stage_min,
                                                     stage_max=stage_max, head_price=head_price, cte_kg=cte_kg,
                                                     cte_fixed=cte_fixed, cte_unit=cte_unit, carry=carry, store=store)
        return sql

    # 买单价和空报价表插入数据
    #   参数:
    #       id:客户工号
    #       em_price:空包价格
    #       unit_price:单价
    #       emun_store:店铺
    def inster_data_toum(id, em_price, unit_price, emun_store, entering_clerk):
        sql = """INSERT INTO customer_quotation.empty_unit(objemun_id, em_price, unit_price, emun_store, entering_clerk)
                 VALUE("75512.{id}", {em_price}, {unit_price}, 
                       "{emun_store}", "{entering_clerk}")""".format(id=id, em_price=em_price,
                                                                     unit_price=unit_price,
                                                                     emun_store=emun_store,
                                                                     entering_clerk=entering_clerk)
        return sql


    # 给id字段创建索引
    #   参数:
    #       id:客户工号
    def create_id_index(id):
        sql = """CREATE INDEX id ON customer_quotation.obj_{id}(id);""".format(id=id)
        return sql


    # 给obj_id字段创建索引
    #   参数:
    #       id:客户工号
    def create_objid_index(id):
        sql = """CREATE INDEX obj_id ON customer_quotation.obj_{id}(obj_id);""".format(id=id)
        return sql


    # 给states字段创建索引
    #   参数:
    #       id:客户工号
    def create_states_index(id):
        sql = """CREATE INDEX states ON customer_quotation.obj_{id}(states);""".format(id=id)
        return sql


    # 给head_kg字段创建索引
    #   参数:
    #       id:客户工号
    def create_headkg_index(id):
        sql = """CREATE INDEX head_kg ON customer_quotation.obj_{id}(head_kg);""".format(id=id)
        return sql


    # 给stage_min字段创建索引
    #   参数:
    #       id:客户工号
    def create_stagemin_index(id):
        sql = """CREATE INDEX stage_min ON customer_quotation.obj_{id}(stage_min);""".format(id=id)
        return sql


    # 给stage_max字段创建索引
    #   参数:
    #       id:客户工号
    def create_stagemax_index(id):
        sql = """CREATE INDEX stage_max ON customer_quotation.obj_{id}(stage_max);""".format(id=id)
        return sql


    # 给head_price字段创建索引
    #   参数:
    #       id:客户工号
    def create_headprice_index(id):
        sql = """CREATE INDEX head_price ON customer_quotation.obj_{id}(head_price);""".format(id=id)
        return sql


    # 给cte_kg字段创建索引
    #   参数:
    #       id:客户工号
    def create_ctekg_index(id):
        sql = """CREATE INDEX cte_kg ON customer_quotation.obj_{id}(cte_kg);""".format(id=id)
        return sql


    # 给cte_fixed字段创建索引
    #   参数:
    #       id:客户工号
    def create_ctefixed_index(id):
        sql = """CREATE INDEX cte_fixed ON customer_quotation.obj_{id}(cte_fixed);""".format(id=id)
        return sql


    # 给carry字段创建索引
    #   参数:
    #       id:客户工号
    def create_cteunit_index(id):
        sql = """CREATE INDEX cte_unit ON customer_quotation.obj_{id}(cte_unit);""".format(id=id)
        return sql


    # 给carry字段创建索引
    #   参数:
    #       id:客户工号
    def create_carry_index(id):
        sql = """CREATE INDEX carry ON customer_quotation.obj_{id}(carry);""".format(id=id)
        return sql


    # 给store字段创建索引
    #   参数:
    #       id:客户工号
    def create_store_index(id):
        sql = """CREATE INDEX store ON customer_quotation.obj_{id}(store);""".format(id=id)
        return sql


    # 更改客户报价表字段数据类型并添加数据长度
    #   参数:
    #       id:客户工号
    def changing_the_data_type(id):
        return ['ALTER TABLE obj_{id} MODIFY id INT(11);'.format(id=id.strip().replace(' ', '')),
                'ALTER TABLE obj_{id} MODIFY head_kg DOUBLE(255,3);'.format(id=id.strip().replace(' ', '')),
                'ALTER TABLE obj_{id} MODIFY stage_min DOUBLE(255,3);'.format(id=id.strip().replace(' ', '')),
                'ALTER TABLE obj_{id} MODIFY stage_max DOUBLE(255,3);'.format(id=id.strip().replace(' ', '')),
                'ALTER TABLE obj_{id} MODIFY head_price DOUBLE(255,3);'.format(id=id.strip().replace(' ', '')),
                'ALTER TABLE obj_{id} MODIFY cte_kg DOUBLE(255,3);'.format(id=id.strip().replace(' ', '')),
                'ALTER TABLE obj_{id} MODIFY cte_fixed DOUBLE(255,3);'.format(id=id.strip().replace(' ', '')),
                'ALTER TABLE obj_{id} MODIFY cte_unit DOUBLE(255,3);'.format(id=id.strip().replace(' ', '')),
                'ALTER TABLE obj_{id} MODIFY carry DOUBLE(255,3);'.format(id=id.strip().replace(' ', ''))]


    # 设置客户报价表主键
    #   参数:
    #       id:客户工号
    def set_the_primary_key(id):
        return "ALTER TABLE obj_{id} ADD PRIMARY KEY (id);".format(id=id.strip().replace(' ', ''))


    # 插入监控用户动作信息
    #   参数:
    #       timestamps:时间戳
    #       actions:用户动作
    #       operators:操作人
    #       internet_ip:公网IP
    #       lan_ipv4:局域网IP
    #       wlan_ipv4:无线局域网IP
    #       lan_mac:局域网MAC地址
    #       wlan_mac:无线局域网MAC地址
    #       place_nation:所在国家
    #       place_state:所在省份
    #       place_city:所在城市
    def logs_messages(timestamps, actions, operators, internet_ip, lan_ipv4,
                      wlan_ipv4, lan_mac, wlan_mac, place_nation, place_state, place_city):

        # 向数据库插入这些信息数据
        sql = """INSERT INTO log_information.logs(timestamps, actions, operators, internet_ip, 
                                                  lan_ipv4, wlan_ipv4, lan_mac, wlan_mac, 
                                                  place_nation, place_state, place_city)
                 VALUE("{timestamps}","{actions}","{operators}","{internet_ip}",
                       "{lan_ipv4}","{wlan_ipv4}","{lan_mac}","{wlan_mac}","{place_nation}",
                       "{place_state}","{place_city}");""".format(timestamps=timestamps,
                                                                  actions=actions,
                                                                  operators=operators,
                                                                  internet_ip=internet_ip,
                                                                  lan_ipv4=lan_ipv4,
                                                                  wlan_ipv4=wlan_ipv4,
                                                                  lan_mac=lan_mac,
                                                                  wlan_mac=wlan_mac,
                                                                  place_nation=place_nation,
                                                                  place_state=place_state,
                                                                  place_city=place_city)
        return sql


    # 创建西北超标报价表
    def creat_nwprice_table(id):
        sql = """CREATE TABLE IF NOT EXISTS customer_quotation_nw.nwobj_{id}(
                        w_id INT(0) PRIMARY KEY AUTO_INCREMENT,
                        objw_id VARCHAR(255),
                        proportion DOUBLE(255,3),
                        head_kg DOUBLE(255,3),
                        stage_min DOUBLE(255,3),
                        stage_max DOUBLE(255,3),
                        head_price DOUBLE(255,3),
                        cte_kg DOUBLE(255,3),
                        cte_fixed DOUBLE(255,3),
                        cte_unit DOUBLE(255,3),
                        carry DOUBLE(255,3),
                        store VARCHAR(255));""".format(id=id.strip().replace(' ', ''))
        return sql

    # 给id字段创建索引
    #   参数:
    #       id:客户工号
    def create_nwid_index(id):
        sql = """CREATE INDEX w_id ON customer_quotation_nw.nwobj_{id}(w_id);""".format(id=id)
        return sql

    # 给obj_id字段创建索引
    #   参数:
    #       id:客户工号
    def create_nwobjid_index(id):
        sql = """CREATE INDEX objw_id ON customer_quotation_nw.nwobj_{id}(objw_id);""".format(id=id)
        return sql

    # 给states字段创建索引
    #   参数:
    #       id:客户工号
    def create_proportion_index(id):
        sql = """CREATE INDEX proportion ON customer_quotation_nw.nwobj_{id}(proportion);""".format(id=id)
        return sql

    # 给head_kg字段创建索引
    #   参数:
    #       id:客户工号
    def create_nwheadkg_index(id):
        sql = """CREATE INDEX head_kg ON customer_quotation_nw.nwobj_{id}(head_kg);""".format(id=id)
        return sql

    # 给stage_min字段创建索引
    #   参数:
    #       id:客户工号
    def create_nwstagemin_index(id):
        sql = """CREATE INDEX stage_min ON customer_quotation_nw.nwobj_{id}(stage_min);""".format(id=id)
        return sql

    # 给stage_max字段创建索引
    #   参数:
    #       id:客户工号
    def create_nwstagemax_index(id):
        sql = """CREATE INDEX stage_max ON customer_quotation_nw.nwobj_{id}(stage_max);""".format(id=id)
        return sql

    # 给head_price字段创建索引
    #   参数:
    #       id:客户工号
    def create_nwheadprice_index(id):
        sql = """CREATE INDEX head_price ON customer_quotation_nw.nwobj_{id}(head_price);""".format(id=id)
        return sql

    # 给cte_kg字段创建索引
    #   参数:
    #       id:客户工号
    def create_nwctekg_index(id):
        sql = """CREATE INDEX cte_kg ON customer_quotation_nw.nwobj_{id}(cte_kg);""".format(id=id)
        return sql

    # 给cte_fixed字段创建索引
    #   参数:
    #       id:客户工号
    def create_nwctefixed_index(id):
        sql = """CREATE INDEX cte_fixed ON customer_quotation_nw.nwobj_{id}(cte_fixed);""".format(id=id)
        return sql

    # 给carry字段创建索引
    #   参数:
    #       id:客户工号
    def create_nwcteunit_index(id):
        sql = """CREATE INDEX cte_unit ON customer_quotation_nw.nwobj_{id}(cte_unit);""".format(id=id)
        return sql

    # 给carry字段创建索引
    #   参数:
    #       id:客户工号
    def create_nwcarry_index(id):
        sql = """CREATE INDEX carry ON customer_quotation_nw.nwobj_{id}(carry);""".format(id=id)
        return sql

    # 给store字段创建索引
    #   参数:
    #       id:客户工号
    def create_nwstore_index(id):
        sql = """CREATE INDEX store ON customer_quotation_nw.nwobj_{id}(store);""".format(id=id)
        return sql


    def inster_data_tonwbill(id, proportion, head_kg,
                             stage_min, stage_max,
                             head_price, cte_kg,
                             cte_fixed, cte_unit,
                             carry, store):
        sql = """INSERT INTO customer_quotation_nw.nwobj_{id}(objw_id, proportion, 
                                                         head_kg, stage_min, 
                                                         stage_max, head_price,
                                                         cte_kg, cte_fixed,
                                                         cte_unit, carry, store)
                 VALUE("75512.{id}", {proportion}, {head_kg},
                       {stage_min}, {stage_max}, {head_price},
                       {cte_kg}, {cte_fixed}, {cte_unit},
                       {carry},"{store}");""".format(id=id, proportion=proportion, head_kg=head_kg, stage_min=stage_min,
                                                     stage_max=stage_max, head_price=head_price, cte_kg=cte_kg,
                                                     cte_fixed=cte_fixed, cte_unit=cte_unit, carry=carry, store=store)
        return sql


# 删除单价表内重复数据
def rmduplicates_um():
    sql = """DELETE 
             FROM customer_quotation.empty_unit c
             WHERE c.emun_id NOT IN(SELECT *
                                    FROM (SELECT v.emun_id
                                    FROM customer_quotation.empty_unit v
                                    GROUP BY v.objemun_id,v.em_price,v.unit_price,v.emun_store) t)"""
    return sql